package dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.CSR;
import model.Person;
import utils.CSRUtility;

public class Crud {

	JDBCManager jdbcmanager;

	public Crud() {
		this.jdbcmanager = new JDBCManager();
	}

	/**
	 * Save a user to database.
	 * 
	 * @param user
	 * @return true when success false otherwise.
	 */
	public boolean register(Person user) throws Exception {
		String query = "INSERT INTO users(lastname,firstname,fonction,tel,company,adress1,adress2,town,postalCode,country,email,password,secretQuestion,response)"
				+ "VALUES('"
				+ user.getFirstName()
				+ "','"
				+ user.getLastName()
				+ "','"
				+ user.getFonction()
				+ "','"
				+ user.getTel()
				+ "','"
				+ user.getCompany()
				+ "','"
				+ user.getAdress1()
				+ "','"
				+ user.getAdress2()
				+ "','"
				+ user.getTown()
				+ "','"
				+ user.getPostalCode()
				+ "','"
				+ user.getCountry()
				+ "','"
				+ user.getEmail()
				+ "','"
				+ user.getPassword()
				+ "','"
				+ user.getSecretQuesion() + "','" + user.getResponse() + "')";
		if (this.jdbcmanager.connect()) {
			int rs = this.jdbcmanager.execUpdate(query);
			this.jdbcmanager.close();

			if (rs == -1)
				return false;
			return true;
		} else {
			return false;
		}
	}

	/**
	 * @param email
	 * @param password
	 * @return Person with email and and password passed in parameters
	 */
	public Person getPerson(String email, String password) throws Exception {
		String query = "SELECT * FROM users WHERE email = '" + email
				+ "' AND password ='" + password + "'";

		Person p = null;

		if (this.jdbcmanager.connect()) {
			ResultSet rs = this.jdbcmanager.execQuery(query);
			if (rs == null)
				return null;
			else {
				while (rs.next()) {
					p = new Person(rs.getInt(1), rs.getString(2),
							rs.getString(3), rs.getString(4), rs.getString(5),
							rs.getString(6), rs.getString(7), rs.getString(8),
							rs.getString(9), rs.getInt(10), rs.getString(11),
							rs.getString(12), rs.getString(13),
							rs.getString(14), rs.getString(15));
					break;
				}
			}
			this.jdbcmanager.close();
			return p;

		} else {
			return null;
		}
	}

	/***
	 * @param userId
	 * @param csr
	 * @return true when success false otherwise.
	 */
	public boolean addCSR(int userId, String csr) throws Exception {

		java.util.Date date = new java.util.Date();
		java.sql.Timestamp timestamp = new Timestamp(date.getTime());

		String query = "INSERT INTO csr(userId,csr,state,date)VALUES('"
				+ userId + "','" + csr + "','0','" + timestamp + "')";
		if (this.jdbcmanager.connect()) {
			int res = this.jdbcmanager.execUpdate(query);
			this.jdbcmanager.close();
			return res > 0 ? true : false;
		}

		return false;
	}

	/**
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public CSR getCSRById(int id) throws Exception {
		String query = "SELECT id,date,state,csr, FROM csr WHERE id = '" + id
				+ "'";
		if (this.jdbcmanager.connect()) {
			ResultSet rs = this.jdbcmanager.execQuery(query);
			while (rs.next()) {
				CSR csr = new CSR(rs.getInt(1), rs.getTimestamp(2),
						rs.getInt(3), CSRUtility.ReadCSR(rs.getString(4)));
				return csr;
			}
			this.jdbcmanager.close();
		}
		return null;
	}

	/**
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CSR> getCSRByUserId(int id) throws Exception {

		String query = "SELECT id,date,state,csr FROM csr WHERE userId = '"
				+ id + "'";
		ArrayList<CSR> csrs = new ArrayList<>();
		if (this.jdbcmanager.connect()) {
			ResultSet rs = this.jdbcmanager.execQuery(query);
			while (rs.next()) {
				CSR csr = new CSR(rs.getInt(1), rs.getTimestamp(2),
						rs.getInt(3), CSRUtility.ReadCSR(rs.getString(4)));

				csrs.add(csr);
			}
			this.jdbcmanager.close();

			return csrs;
		}
		return null;
	}

	public String getCACertificate() throws Exception {
		String cert = "";
		if (jdbcmanager.connect()) {
			String query = "select certificate from ca";
			ResultSet rs = jdbcmanager.execQuery(query);
			while (rs.next()) {
				cert = rs.getString(1);
				break;
			}
			jdbcmanager.close();
		}
		return cert;
	}

	public String getUserCertificate(int userId, int csrId) throws Exception {
		String cert = "";
		if (jdbcmanager.connect()) {
			String query = "select certificate from certificates where userId ='"
					+ userId + "' and csrId ='" + csrId + "'";
			ResultSet rs = jdbcmanager.execQuery(query);
			while (rs.next()) {
				cert = rs.getString(1);
				break;
			}
			jdbcmanager.close();
		}
		return cert;
	}
}
